package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBSaede 
{
	/**
	 * Initializes instance variables
	 */
	private Connection con;

	/** Creates connection to the database and creates an instance of DBSaede*/
	public DBSaede()
	{
		con = DbConnection1.getInstance().getDBcon();
	}

	/** getAllSaede finds all Saeder with
	  * @param retrieveAssociation (tells weather or not the returned Saede
	  *  needs to be associated with a Sal)
	  * @return
	  */
	public ArrayList<Saede> getAllSaede(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);	
	}
	
	/** getSaede finds a specific saede with
	 * @param saedeId
	 * @param retrieveAssociation (tells weather or not the returned Forestillinger
	  *  needs to be associated with a Film and a Sal)
	 * @return
	 */
	public Saede getSaede(int saedeId, boolean retriveAssociation)
	{
		String wClause = "saedeId = '" + saedeId + "'";
		//System.out.println("SearchSaede " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	
	/** insertSaede creates a saede SQL query to be inserted into database with
	 * 
	 * @param saede
	 * @return
	 * @throws Exception
	 */
	public int insertSaede(Saede saede) throws Exception
	{  //call to get the next personID number
		int nextSaedeId = GetMax.getMaxId("Select max(saedeId) from saede");
		nextSaedeId++;
		//System.out.println("next id = " +  nextSaedeId);

		int rc = -1;
		String query="INSERT INTO saede(saedeId, saedeNr, raekkeNr, saedeStatus, sal)  VALUES('"+
				
				nextSaedeId  + "','"  +
				saede.getSaedeNr()  + "','"  +
				saede.getRaekkeNr()  + "','"  +
				saede.getSaedeStatus()  + "','"  +
				saede.getSal().getSalNr()  + "')";


		//System.out.println("insert : " + query);
		try
		{ // insert new saede
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			//System.out.println("Saede ikke oprettet");
			throw new Exception ("Saede is not inserted correct");
		}
		return(rc);
	}
	
	/** deleteSaede removes a Saede from database with
	 * 
	 * @param saedeId
	 * @return
	 */
	public int deleteSaede(int saedeId)
	{
		int rc=-1;

		String query="DELETE FROM saede WHERE saedeId = '" +
				saedeId + "'";
		//System.out.println(query);
		try
		{ // delete from saede
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex)
		{
			//System.out.println("Delete exception in Saede db: "+ex);
		}
		return(rc);
	}
	
	/** updateForestilling creates an update saede SQL query to update a saede in the database with
	 * 
	 * @param saede
	 * @return
	 */
	public int updateSaede(Saede saede)
	{
		Saede saedeObj  = saede;
		int rc=-1;

		String query="UPDATE saede SET "+
				"saedeNr ='"+ saedeObj.getSaedeNr() + "', " +
				"raekkeNr ='"+ saedeObj.getRaekkeNr() + "', " +
				"saedeStatus ='"+ saedeObj.getSaedeStatus() + "', " +
				"sal ='"+ saedeObj.getSal().getSalNr() + "' " +
				"WHERE saedeId = '"+ saedeObj.getSaedeId() + "'";
		//System.out.println("Update query:" + query);
		try
		{ // update person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Update exception in person db: "+ex);
		}
		return(rc);
	}
	
	/** singleWhere is used when we only want to select one Saede
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Saede
	 *  needs to be associated with Sal)
	 * @return
	 */
	private Saede singleWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		Saede saede = new Saede();	

		String query =  buildQuery(wClause);
		//System.out.println(query);
		try
		{ // read the saede from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next() )
			{
				saede = buildSaede(results);
				
				stmt.close();
				if(retrieveAssociation)
				{   
					//association to Sal
					DBSal dbSal = new DBSal();
					Sal sal = dbSal.getSal(saede.getSal().getSalNr(), false);
					//System.out.println("Sal is seleceted ");
					saede.setSal(sal);
				}			
			}
			else
			{ //if no saede found
				saede = null;
			}
		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception: "+e);
		}
		return saede;
	}

	/** mischWere is used whenever we want to select more than one Saede or all Saeder
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Saeder needs to be associated with a Sal)
	 * @return
	 */
	private ArrayList<Saede> miscWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		ArrayList<Saede> list = new ArrayList<Saede>();	
		String query =  buildQuery(wClause);

		try
		{ // read the saede from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() )
			{
				Saede SaedeObj = new Saede();
				SaedeObj = buildSaede(results);	
				list.add(SaedeObj);	
			}//end while
			stmt.close();     
			if(retrieveAssociation)
			{   
				for(Saede SaedeObj : list)
				{
					DBSal dbSal = new DBSal();
					Sal sal = dbSal.getSal(SaedeObj.getSal().getSalNr(), false);
					//System.out.println("Sal is seleceted ");
					SaedeObj.setSal(sal);
				}
			}//end if   

		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a Saede and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause) 
	{
		String query="SELECT * FROM Saede";

		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}
		return query;
	}

	/** buildSaede creates a saede object with
	 * 
	 * @param results (used to bring attributes to saedeObj)
	 * @return
	 */
	private Saede buildSaede(ResultSet results) 
	{
		Saede saedeObj = new Saede();
		try
		{ // the columns from the table person  are used
			saedeObj.setRaekkeNr(results.getByte("raekkeNr"));
			saedeObj.setSaedeId(results.getInt("saedeId"));
			saedeObj.setSaedeNr(results.getInt("saedeNr"));
			saedeObj.setSaedeStatus(results.getBoolean("saedeStatus"));
			saedeObj.setSal(new Sal(results.getByte("sal")));
		}
		catch(Exception e)
		{
			//System.out.println("error in building the person object");
		}
		return saedeObj;
	}

}

